<?php

/**
 * @author Thibaut Cromphaut, Gaïtano Boeraeve, Mathias Helin, Dimitry Dierickx
 * @copyright  Copyright (c) 2011 Artevelde University College Ghent 
 */

class Application_Model_CourseMapper extends Ahs_Model_Mapper
{

    public function __construct(array $options = null)
    {
        parent::__construct($options);
        $this->DbTableName = 'Application_Model_DbTable_Courses';
        $this->PropertiesMap    = array(
            "Id"            => "course_id",
            "Name"          => "course_name",
            "Image"         => "course_image",
            "Description"   => "course_description",
            "CreationDate"  => "course_creationdate",
            "ModifiedDate"  => "course_modifieddate",
            "DeletedDate"   => "course_deleteddate",
            "AuthorId"      => "course_author_id",
        );
    }
    
    /**
     * Get all courses
     * 
     * @param integer $page_nr      If you wish to use paging, pass the page-number. To disable paging, 
     *                              pass null as value (default) and return all rows.
     * @param integer $nr_results   The number of results per page, defaults to 10.
     * @return Array(Application_Model_Course)
     */
    public function fetchAll($page_nr=null, $nr_results=10)
    {
        $table = $this->getDbTable();
        $select = $table->select();
        $select->where(''.$this->getColumn('DeletedDate').' IS NULL')
               ->order(''.$this->getColumn('CreationDate').' DESC');
        
        if($page_nr != null && $page_nr > 0)
            $select->limitPage($page_nr, $nr_results);
        
        $rows = $table->fetchAll($select);
        return $this->_toCourses($rows);
    }

    /**
     * Get a course by ID
     * 
     * @param integer $course_id
     * @return Application_Model_Course
     */
    public function fetchById($course_id)
    {
        $table = $this->getDbTable();
        $select = $table->select();
        $select->where(
                    ''.$this->getColumn('Id').' = :id'
                        . ' AND ' .
                    ''.$this->getColumn('DeletedDate').' IS NULL'
                )
                ->bind(array(':id' => $course_id));
        $row = $table->fetchRow($select);    
        return $this->_toCourse($row);
    }
    
    /**
     * Get all courses for an author
     * 
     * @return Array(Application_Model_Course)
     */
    public function fetchByAuthorId($author_id, $page_nr=null, $nr_results=10)
    {
        $table = $this->getDbTable();
        $select = $table->select()->setIntegrityCheck(false);
        $select->where(
                    ''.$this->getColumn('AuthorId').' = :id'
                       . ' AND ' .
                    ''.$this->getColumn('DeletedDate').' IS NULL'
                 )
               ->bind(array(':id' => $author_id))
               ->order(''.$this->getColumn('CreationDate').' DESC');
        
        if($page_nr != null && $page_nr > 0)
            $select->limitPage($page_nr, $nr_results);
        
        $rows = $table->fetchAll($select);
        return $this->_toCourses($rows);
    }
    
    /**
     * Get all courses by name for an author
     * 
     * @return Array(Application_Model_Course)
     */
    public function fetchByNameAndAuthorId($course_name, $author_id)
    {
        $table = $this->getDbTable();
        $select = $table->select()->setIntegrityCheck(false);
        $select->where(
                    ''.$this->getColumn('Name').'  LIKE :name'                    
                       . ' AND ' .
                    ''.$this->getColumn('AuthorId').' = :id'
                       . ' AND ' .
                    ''.$this->getColumn('DeletedDate').' IS NULL'
                 )
               ->bind(array(':name' => "%{$course_name}%", ':id' => $author_id))
               ->order(''.$this->getColumn('Name').' ASC');
        $rows = $table->fetchAll($select);
        return $this->_toCourses($rows);
    }
    
    /**
     * Get the courses the user is subscribed to
     * 
     * @param integer $user_id
     * @param integer $page_nr      If you wish to use paging, pass the page-number. To disable paging, 
     *                              pass null as value (default) and return all rows.
     * @param integer $nr_results   The number of results per page, defaults to 10.
     * @return Array(Application_Model_Course)
     */
    public function fetchByUserId($user_id, $page_nr=null, $nr_results=10)
    {
        $table = $this->getDbTable();
        $select = $table->select()->setIntegrityCheck(false);
        $select->from('courses')->joinInner(array('uhc' => 'users_has_courses'),'courses.course_id=uhc.courses_course_id')
               ->where(
                    'uhc.users_user_id = :id'
                       . ' AND ' .
                    ''.$this->getColumn('DeletedDate').' IS NULL'
                 )
               ->bind(array(':id' => $user_id))
               ->order(''.$this->getColumn('CreationDate').' DESC');
        
        if($page_nr != null && $page_nr > 0)
            $select->limitPage($page_nr, $nr_results);
        
        $rows = $table->fetchAll($select);
        
        return $this->_toCourses($rows);
    }
    
    /**
     * Get all recent courses
     * 
     * @param integer $days         The number of days to go back in time to fetch items.
     * @param integer $page_nr      If you wish to use paging, pass the page-number. To disable paging, 
     *                              pass null as value (default) and return all rows.
     * @param integer $nr_results   The number of results per page, defaults to 10.
     * @return Array(Application_Model_Course) 
     */
    public function fetchByRecent($days=30, $page_nr=null, $nr_results=10)
    {
        $interval   = new Zend_Db_Expr('DATE_SUB(CURDATE(), INTERVAL '.$days.' DAY)');
        $table = $this->getDbTable();
        $select = $table->select();
        $select->where(
                    ''.$this->getColumn('CreationDate').' > '.$interval
                        . ' AND ' .
                    ''.$this->getColumn('DeletedDate').' IS NULL'
                )
               ->order(''.$this->getColumn('CreationDate').' DESC');
        
        if($page_nr != null && $page_nr > 0)
            $select->limitPage($page_nr, $nr_results);
        
        $rows = $table->fetchAll($select);
        return $this->_toCourses($rows);
    }
    
    /**
     * Get the total number of courses
     * 
     * @return integer
     */
    public function getCount()
    {
        $ids    = array();
        
        $table = $this->getDbTable();
        $select = $table->select();
        $select->from($table, array('count(*) as count'));
        $select->where(''.$this->getColumn('DeletedDate').' IS NULL');
        
        $rows = $table->fetchAll($select);
        
        return $rows[0]->count;
    }
    
    /**
     * Get the total number of courses for this user is subscribed to
     * 
     * @param integer $user_id
     * @return integer
     */
    public function getCountByUserId($user_id)
    {
        $table = $this->getDbTable();
        $select = $table->select()->setIntegrityCheck(false);
        $select->from($table, array('count(*) as count'));
        $select->joinInner(array('uhc' => 'users_has_courses'),'courses.course_id=uhc.courses_course_id')
               ->where(
                    'uhc.users_user_id = :id'
                       . ' AND ' .
                    'courses.'.$this->getColumn('DeletedDate').' IS NULL'
                 )
               ->bind(array(':id' => $user_id));
        $rows = $table->fetchAll($select);
        
        return $rows[0]->count;
    }
    
    /**
     * Get the total number of courses for this author
     * 
     * @param integer $author_id
     * @return integer
     */
    public function getCountByAuthorId($author_id)
    {
        $table = $this->getDbTable();
        $select = $table->select()->setIntegrityCheck(false);
        $select->from($table, array('count(*) as count'));
        $select->where(
                    ''.$this->getColumn('AuthorId').' = :id'
                       . ' AND ' .
                    ''.$this->getColumn('DeletedDate').' IS NULL'
                 )
               ->bind(array(':id' => $author_id));
        $rows = $table->fetchAll($select);
        
        return $rows[0]->count;
    }
    
    /**
     * Get the total number of recent courses
     * 
     * @param integer $days         The number of days to go back in time to fetch items.
     * @return integer
     */
    public function getCountByRecent($days=30)
    {
        $interval   = new Zend_Db_Expr('DATE_SUB(CURDATE(), INTERVAL '.$days.' DAY)');
        $table = $this->getDbTable();
        $select = $table->select();
        $select->from($table, array('count(*) as count'));
        $select->where(
                    ''.$this->getColumn('CreationDate').' > ('.$interval.')'
                        . ' AND ' .
                    ''.$this->getColumn('DeletedDate').' IS NULL'
                );
        $rows = $table->fetchAll($select);
        
        return $rows[0]->count;
    }
    
    /**
     * Search for courses by name
     * 
     * @return Array(Application_Model_Course)
     */
    public function fetchByName($course_name)
    {
        $table = $this->getDbTable();

        $select = $table->select();
        $select->where(
                    ''.$this->getColumn('Name').' LIKE :course'
                        . ' AND ' .
                    ''.$this->getColumn('DeletedDate').' IS NULL'
                )
               ->bind( array(':course' => "%{$course_name}%") )
               ->order(''.$this->getColumn('Name').' ASC')
               ->limit(10);
        $rows = $table->fetchAll($select);
        return $this->_toCourses($rows);
    }
    
    /**
     * Get an array of course_ids for a user
     * 
     * @return Array(course_id)
     */
    public function getCourseIdsByUserId($user_id)
    {
        $ids    = array();
        
        $table = $this->getDbTable();
        $select = $table->select()->setIntegrityCheck(false);
        $select->from('courses')->joinInner(array('uhc' => 'users_has_courses'),'courses.course_id=uhc.courses_course_id')
               ->where(
                    'uhc.users_user_id = :id'
                       . ' AND ' .
                    ''.$this->getColumn('DeletedDate').' IS NULL'
                 )
               ->bind(array(':id' => $user_id));
        $rows = $table->fetchAll($select);
        
        if($rows != null && count($rows))
        {
            foreach($rows as $row)
            {
                $ids[]  = $row[''.$this->getColumn('Id').''];
            }
        }
        
        return $ids;
    }

    /**
     * Save a course
     *
     * @param Application_Model_Course $course
     */
    public function save(Application_Model_Course $course)
    {
        $course_id  = $course->Id;
        
        $data = $this->_getTableValues($course, array("Id"));
        $data[$this->getColumn("ModifiedDate")] = new Zend_Db_Expr('NOW()');
 
        if (null == $course_id)
        {
            // Set some default values
            $data[$this->getColumn("CreationDate")] = new Zend_Db_Expr('NOW()');
            $data[$this->getColumn("AuthorId")]     = $this->getLoggedInUserId();
            
            // Insert and get the id
            $course_id    = $this->getDbTable()->insert($data);
            
            // Add this course to the user's courses
            $mapperUser = new Application_Model_UserMapper();
            $mapperUser->addCourseToUser($course_id, $this->getLoggedInUserId());
            
        }
        else
        {
            $this->getDbTable()->update($data, array(''.$this->getColumn('Id').' = ?' => $course_id));
        }
        
        return $course_id;
    }
    
    /**
     * Delete a course by id
     *
     * @param integer $id
     */
    public function delete($id)
    {
        if(null != $id)
        {
            $data[$this->getColumn('DeletedDate')] = new Zend_Db_Expr('NOW()');
            $this->getDbTable()->update($data, array(''.$this->getColumn('Id').' = ?' => $id));
        }
    }
    
    /**
     * Undelete a course by id
     *
     * @param integer $id
     */
    public function undelete($id)
    {   
        if (null != $id)
        {
            $data[$this->getColumn('DeletedDate')] = new Zend_Db_Expr('NULL');
            $this->getDbTable()->update($data, array(''.$this->getColumn('Id').' = ?' => $id));
        } 
    }
    
    /**
     * Convert a row to object
     * 
     * @param Zend_Db_Table_Row_Abstract $row
     * @return Application_Model_Course
     */
    private function _toCourse(Zend_Db_Table_Row_Abstract $row = null)
    {
        $object = new Application_Model_Course();
        
        if ($row)
        {
            foreach($this->PropertiesMap as $key=>$column)
            {
                if(isset($row[$column]))
                    $object->$key      = $row[$column];
            }
        }

        return $object;
    }

    /**
     * Convert a rowset to an Array of objects
     * 
     * @param Zend_Db_Table_Rowset_Abstract $rows
     * @return Array(Application_Model_Course)
     */
    private function _toCourses(Zend_Db_Table_Rowset_Abstract $rows = null)
    {
        $courses = array();

        if ($rows)
        {
            foreach ($rows as $row)
            {
                $courses [] = $this->_toCourse($row);
            }
        }
        
        return $courses;
    }

}